package com.v8dn.app.util.excel;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.v8dn.app.util.DateUtil;

/**
 * @title Excel写工具类
 * @author lhf
 * @date 2012-10-31 下午12:29:07
 */
public class ExcelWriteUtil{

	public static void main(String[] args){
		/****创建exlce****/
		String outPutPath="d:/";
		Workbook wb=ExcelWriteUtil.createWorkBook(7);
		Sheet sheet=ExcelWriteUtil.createSheet(wb,"你好世界");
		Sheet sheet1=ExcelWriteUtil.createSheet(wb,"222");
		Row row=ExcelWriteUtil.createRow(sheet,0,500);
		Cell cell=ExcelWriteUtil.createCell(row,1,null);
		cell.setCellValue("张三");
		ExcelWriteUtil.writeWorkbook(wb,outPutPath);
	}
	
	
	private static Log log=LogFactory.getLog(ExcelWriteUtil.class);

	static{
		log.debug("启动ExcelWrite工具类!");
	}

	/**
	 * TODO 私用构造主法.因为此类是工具类.
	 */
	public ExcelWriteUtil(){
		super();
	}

	/**
	 * @title 根据传进来的对象判断是03还是07
	 * @param obj
	 * @return 0:不是poi对象,3:03对象,7:07对象
	 */
	private static int getVersion(Object obj){
		if(obj.getClass().getSimpleName().substring(0,1).equals("H")){
			return 3;
		}else if(obj.getClass().getSimpleName().substring(0,1).equals("X")){
			return 7;
		}else{
			return 0;
		}
	}

	/**
	 * @title 创建文件
	 * @param filePath 文件路径
	 * @return File
	 */
	private static File createFile(String filePath){
		return new File(filePath);
	}
	
	/**
	 * @title 获取文件输出流
	 * @param filePath 文件路径
	 * @return FileOutputStream
	 */
	private static FileOutputStream createFileOutputStream(String filePath){
		try{
			return new FileOutputStream(createFile(filePath));
		}catch(FileNotFoundException e){
			// TODO Auto-generated catch block
			log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
		}
		return null;
	}
	
	/**
	 * @title 将Workbook写入Excel文件
	 * @param wb Workbook
	 * @param outPutPath 写入文件的相对路径目录
	 */
	public static void writeWorkbook(Workbook wb,String outPutPath){
		FileOutputStream fos=null;
		if(getVersion(wb)==3){
			fos=createFileOutputStream(outPutPath+DateUtil.format(new Date(),DateUtil.fullFormat1)+".xls");
		}else if(getVersion(wb)==7){
			fos=createFileOutputStream(outPutPath+DateUtil.format(new Date(),DateUtil.fullFormat1)+".xlsx");
		}else{
			log.error(new StringBuffer("[excel版本有误,请重新再试!!!]"));
		}
		try{
			wb.write(fos);
		}catch(FileNotFoundException e){
			log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
		}catch(IOException e){
			log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
		}finally{
			try{
				if(fos!=null){
					fos.close();
				}
			}catch(IOException e){
				log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
			}
		}
	}
	
	/**
	 * @title 创建WorkBook
	 * @param version 版本
	 * @return Workbook
	 */
	public static Workbook createWorkBook(int version){
		try{
			if(version==3){// 03版本
				return new HSSFWorkbook();
			}else if(version==7){// 07版本
				return new XSSFWorkbook();
			}else{
				return null;
			}
		}catch(Exception e){
			// TODO: 处理异常
			log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
		}
		return null;
	}
	
	/**
	 * @title 创建Sheet工作簿
	 * @param wb Workbook
	 * @param sheetName sheet页名称
	 * @return Sheet
	 */
	public static Sheet createSheet(Workbook wb,String sheetName){
		Sheet sheet=wb.createSheet(sheetName);
		sheet.setDefaultColumnWidth(12);//列宽
		sheet.setDisplayGridlines(true);//启动网格线
		return sheet;
	}
	
	/**
	 * @title 创建Row
	 * @param sheet Sheet
	 * @param rowNum 行数(从0开始)
	 * @param height 高度
	 * @return Row
	 */
	public static Row createRow(Sheet sheet,int rowNum,int height){
		Row row=sheet.createRow(rowNum);//创建当前行
		row.setHeight((short)height);//设置行高
		return row;
	}
	
	/**
	 * @title 创建CELL
	 * @param row Row
	 * @param cellNum 当前单元格数(从0开始)
	 * @param style 样式
	 * @return Cell
	 */
	public static Cell createCell(Row row,int cellNum,CellStyle style){
		Cell cell=row.createCell(cellNum);
		if(style!=null){
			cell.setCellStyle(style);
		}
		return cell;
	}
	
	/**
	 * @title 创建CellStyle样式
	 * @param wb Workbook
	 * @param backgroundColor 背景色
	 * @param foregroundColor 前置色
	 * @param font 字体
	 * @return CellStyle
	 */
	public static CellStyle createCellStyle(Workbook wb,short halign,Font font){
		CellStyle cs=wb.createCellStyle();
		cs.setAlignment(halign);//左右居中
		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs.setFont(font);//字体
		return cs;
	}

	/**
	 * @title 创建带边框的CellStyle样式
	 * @param wb Workbook
	 * @param backgroundColor 背景色
	 * @param foregroundColor 前置色
	 * @param font 字体
	 * @return CellStyle
	 */
	public static CellStyle createBorderCellStyle(Workbook wb,short backgroundColor,short foregroundColor,short halign,Font font){
		CellStyle cs=wb.createCellStyle();
		cs.setAlignment(halign);
		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cs.setFillBackgroundColor(backgroundColor);
		cs.setFillForegroundColor(foregroundColor);
		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs.setFont(font);
		cs.setBorderLeft(CellStyle.BORDER_DASHED);
		cs.setBorderRight(CellStyle.BORDER_DASHED);
		cs.setBorderTop(CellStyle.BORDER_DASHED);
		cs.setBorderBottom(CellStyle.BORDER_DASHED);
		return cs;
	}

	/**
	 * @title 合并单元格
	 * @param sheet Sheet
	 * @param firstRow int
	 * @param lastRow int
	 * @param firstColumn int
	 * @param lastColumn int
	 * @return int 合并区域号码
	 */
	public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstColumn,int lastColumn){
		return sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn));
	}

	/**
	 * @title 创建字体
	 * @param wb Workbook
	 * @param boldweight short
	 * @param color short
	 * @return Font
	 */
	public static Font createFont(Workbook wb,short boldweight,short color,short size){
		Font font=wb.createFont();
		font.setBoldweight(boldweight);
		font.setColor(color);
		font.setFontHeightInPoints(size);
		return font;
	}

	/**
	 * @title 设置合并单元格的边框样式
	 * @param sheet Sheet
	 * @param ca CellRangAddress
	 * @param style CellStyle
	 */
	public static void setRegionStyle(Sheet sheet,CellRangeAddress ca,CellStyle style){
		for(int i=ca.getFirstRow();i<=ca.getLastRow();i++){
			Row row=CellUtil.getRow(i,sheet);
			for(int j=ca.getFirstColumn();j<=ca.getLastColumn();j++){
				Cell cell=CellUtil.getCell(row,j);
				cell.setCellStyle(style);
			}
		}
	}
	
	
}
